package com.food.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import com.food.dao.in.ApplicationDAO;
import com.food.model.Application;


public class ApplicationDAOJdbc implements ApplicationDAO {

//	private static final String URL = "jdbc:sqlserver://192.168.34.108:1433;database=PJDB";
//	private static final String USER = "sa";
//	private static final String PASSWORD = "sa123456";
	private DataSource ds = null;
	public ApplicationDAOJdbc() {
		try {
			Context ctx = new InitialContext();
			this.ds = (DataSource) ctx.lookup("java:comp/env/jdbc/food");
		} catch (NamingException e) {
			e.printStackTrace();
		}
	}
	
	private static final String INSERT = "insert into application (mbid, cid) values(?,?)";

	@Override
	public void insertApplication(Application bean) {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {

//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			stmt = conn.prepareStatement(INSERT);

			stmt.setInt(1, bean.getMbid());
			stmt.setInt(2, bean.getCid());

			stmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

	}

	private static final String DELETE = "delete from application where apid=?";

	@Override
	public void deleteApplication(int apid) {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {

//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			stmt = conn.prepareStatement(DELETE);

			stmt.setInt(1, apid);

			stmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

	}

	private static final String SELECT_BY_ALL = "select apid, mbid, cid from application";

	@Override
	public List<Application> findAllApplication() {
		List<Application> result = null;

		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rset = null;

		try {

//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			stmt = conn.prepareStatement(SELECT_BY_ALL);

			rset = stmt.executeQuery();

			result = new ArrayList<Application>();
			while (rset.next()) {
				Application temp = new Application();
				temp.setApid(rset.getInt("apid"));
				temp.setMbid(rset.getInt("mbid"));
				temp.setCid(rset.getInt("cid"));

				result.add(temp);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rset != null) {
				try {
					rset.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
	}

	private static final String SELECT_BY_MBID = "select apid, mbid, cid from application where mbid=?";

	@Override
	public List<Application> findApplicationByMb(int mbid) {
		List<Application> result = null;

		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rset = null;
		try {

//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			stmt = conn.prepareStatement(SELECT_BY_MBID);
			stmt.setInt(1, mbid);
			rset = stmt.executeQuery();

			result = new ArrayList<Application>();
			while (rset.next()) {
				Application temp = new Application();
				temp.setApid(rset.getInt("apid"));
				temp.setMbid(rset.getInt("mbid"));
				temp.setCid(rset.getInt("cid"));

				result.add(temp);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rset != null) {
				try {
					rset.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
	}

	private static final String SELECT_BY_CID = "select apid, mbid, cid from application where cid=?";

	@Override
	public List<Application> findApplicationByCourse(int cid) {
		List<Application> result = null;

		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rset = null;
		try {

//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			stmt = conn.prepareStatement(SELECT_BY_CID);
			stmt.setInt(1, cid);
			rset = stmt.executeQuery();

			result = new ArrayList<Application>();
			while (rset.next()) {
				Application temp = new Application();
				temp.setApid(rset.getInt("apid"));
				temp.setMbid(rset.getInt("mbid"));
				temp.setCid(rset.getInt("cid"));

				result.add(temp);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rset != null) {
				try {
					rset.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
	}

	public static void main(String[] args) {
		// select cid
		ApplicationDAO dao = new ApplicationDAOJdbc();
		List<Application> bean = dao.findApplicationByCourse(2);
		System.out.println(bean);

		// select mbid
		List<Application> bean1 = dao.findApplicationByMb(1);
		System.out.println(bean1);

		// SELECT ALL
		List<Application> bean2 = dao.findAllApplication();
		System.out.println(bean2);

		// delete
		dao.deleteApplication(6);

		// insert
		Application bean3 = new Application();
		bean3.setMbid(4);
		bean3.setCid(1);
		dao.insertApplication(bean3);
		System.out.println(bean3);

	}
}
